package dao.impl;

import dao.BackMessageDao;
import org.apache.poi.ss.formula.functions.T;
import pojo.BackMessage;
import pojo.FontMessage;
import util.BaseDao;
import util.PageBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 前台消息接口实现类
 *
 * @author 何栋
 */
public class BackMessageDaoImpl implements BackMessageDao {
    /*全局对象*/
    PageBean pageBean = new PageBean();//获取分页对象

    Connection connection = null;//获取连接对象
    PreparedStatement statement = null;//获取发送sql的statement对象
    ResultSet resultSet = null;//获得结果集对象
    List<BackMessage> list = new ArrayList<>();

    /**
     * 查询信息条数，分页所用
     * @param state 根据信息状态条件查询
     * @return  result 返回查询到的信息条数
     */
    public int getDateCount(int state,int uId) {
        int result = 0;
        StringBuffer buffer = new StringBuffer();
        buffer.append("SELECT COUNT(MESSAGE_ID) ROWCOUNT FROM BACK_MESSAGE WHERE 1=1 AND U_ID="+uId);
        //若消息状态为已读（state=1）或未读（state=0），则根据消息状态查询,否则查询所有信息的条数
        if (state != 0 && state != 1) {
            buffer.append(" AND STATE=1 or STATE=0");//查询所有信息信息条数
        } else {
            buffer.append(" AND STATE=" + state);//根据state查询各状态下的信息条数
        }

        try {
            connection= BaseDao.getConnection();
            statement = connection.prepareStatement(buffer.toString());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                result = resultSet.getInt("ROWCOUNT");//根据别名获得查询结果（信息条数）
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(resultSet,statement,connection);
        }
        return result;
    }

    /**
     * 分页查询前台消息
     * @param pageBean 分页信息
     * @return 返回分页信息pageBean
     */
    @Override
    public PageBean<T> getDataList(PageBean pageBean, int state,int uId) {
        if (0!=state&&1!=state){
            state=3;//如果信息状态不为0或1则将其设置为不为0或1的数（此处选择3）
        }
        pageBean.setTotalCount(this.getDateCount(state,uId));//为分页信息类pageBean添加数据总数
        if (pageBean.getPageSize()==0){
            pageBean.setPageSize(1);
        }//防止设置总页数pages时除数为零
        //pages:总页数,若每页数据条数能被数据总量整除，则总页数为两者整除的结果，否则为结果+1
        int pages=this.getDateCount(state,uId)%pageBean.getPageSize()==0 ? this.getDateCount(state,uId)/pageBean.getPageSize():this.getDateCount(state,uId)/pageBean.getPageSize();
        pageBean.setTotalPage(this.getDateCount(state,uId));//设置总页数
        pageBean.setTotalPage(pages);
        int beginRow = (pageBean.getPageIndex() - 1) * pageBean.getPageSize() + 1;
        int endRow = pageBean.getPageIndex() * pageBean.getPageSize();
        BackMessage backMessage = null;//获取前台信息表对象
        StringBuffer buffer = new StringBuffer();
        buffer.append("SELECT * FROM(" +
                "SELECT ROWNUM R,M.* FROM Back_Message M WHERE U_ID="+uId+" AND "
        );
        if (0!=state&&1!=state){//如果信息状态不为0或1则查询所有数据
            buffer.append(" M.State=1 or M.State=0 ");
        }else {
            buffer.append("M.state="+state);
        }
        buffer.append(" ) WHERE R BETWEEN "+beginRow+" AND "+endRow);
        connection = BaseDao.getConnection();
        try {

            statement = connection.prepareStatement(buffer.toString());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                backMessage = new BackMessage();//每获得一行数据重新创建一个BackMessage的对象

                backMessage.setMessageId(resultSet.getInt("MESSAGE_ID"));

                backMessage.setMessage(resultSet.getString("MESSAGE"));

                backMessage.setUId(resultSet.getInt("U_ID"));

                backMessage.setTitle(resultSet.getString("TITLE"));

                backMessage.setSendTime(resultSet.getTimestamp("SEND_TIME"));

                backMessage.setState(resultSet.getInt("STATE"));

                list.add(backMessage);//将BackMessage对象添加到集合list中
            }
            pageBean.setList(list);//将当前页的数据放到pageBean中
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(resultSet,statement,connection);
        }

        return pageBean;
    }

    /**
     * 添加后台消息
     *
     * @return 返回插入结果（插入数据成功的行数）result
     * 传入fintMessage对象(其中保存有需要插入的信息)
     * @auther 何栋
     */
    @Override
    public int addOne(BackMessage backMessage) {
        int result = 0;//插入结果（插入数据成功的行数）
        StringBuffer buffer = new StringBuffer();
        buffer.append("INSERT INTO BACK_MESSAGE (MESSAGE_ID,MESSAGE,U_ID,TITLE,SEND_TIME,STATE,SEND_RECEIVE)" +
                "VALUES" +
                "(SEQ_FONT_ID.NEXTVAL,?,?,?,sysdate,1,?)");
        connection = BaseDao.getConnection();
        try {
            statement = connection.prepareStatement(buffer.toString());
            statement.setString(1, backMessage.getMessage());

            statement.setInt(2, backMessage.getUId());

            statement.setString(3, backMessage.getTitle());

            statement.setInt(4,backMessage.getSendReceive());

            result = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.release(resultSet, statement, connection);
        }

        return result;
    }





    /**
     * 修改信息状态
     * @param messageId 根据消息id修改
     * @param state 修改状态
     * @return
     */
    @Override
    public int modifyState(int[] messageId,int state) {
        int result=0;//接收修改成的行数
        StringBuffer buffer=new StringBuffer();
        buffer.append("UPDATE BACK_MESSAGE SET STATE="+state+" WHERE MESSAGE_ID="+messageId[0]);
        if (messageId.length>1){

            for (int i=1;i<messageId.length;i++){
                buffer.append(" OR MESSAGE_ID="+messageId[i]);
            }
        }
        try {
            connection=BaseDao.getConnection();
            statement=connection.prepareStatement(buffer.toString());
            result= statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(resultSet,statement,connection);
        }
        return result;
    }

    /**
     * 删除消息
     * @param messageId 根据信息id删除
     * @return
     */
    @Override
    public int deleteOne(int messageId) {
        int result=0;//接收修改成功的行数
        StringBuffer buffer=new StringBuffer();
        buffer.append("DELETE FROM BACK_MESSAGE WHERE MESSAGE_ID="+messageId);

        try {
            connection=BaseDao.getConnection();
            statement=connection.prepareStatement(buffer.toString());
            result= statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(resultSet,statement,connection);
        }
        return result;
    }


}
